package dao.impl.LibraryDaoImpl;

import dao.LibraryDao.BookDao;
import common.model.Entity.libraryEntity.Book;
import common.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;
import java.util.Optional;

/**
 * BookDao 实现类
 */
public class BookDaoImpl implements BookDao{

    private static final Logger logger = LoggerFactory.getLogger(BookDaoImpl.class);

    private final QueryRunner queryRunner;

    // 默认构造函数
    public BookDaoImpl(){
        queryRunner = new QueryRunner();
    }

    // 带有 QueryRunner 的构造函数，供单元测试和依赖注入框架使用
    public BookDaoImpl(QueryRunner queryRunner){
        this.queryRunner = queryRunner;
    }

    // 已改
    private void setBookSql(Book book,PreparedStatement ps) throws SQLException{
        ps.setString(1, book.getTitle());
        ps.setString(2, book.getAuthor());
        ps.setString(3,book.getPublisher());
        ps.setString(4,book.getIsbn());
        ps.setInt(5,book.getStock());
        ps.setString(6,book.getCategory());
        ps.setInt(7,book.getAvailable());
        ps.setInt(8,0);
    }


    /**
     * 保存图书
     */
    @Override
    public boolean save(Book book) {
        String sql = "INSERT INTO t_book"+" (title,author,publisher,isbn,stock,category,available,is_deleted) " +"VALUES (?,?,?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);      // 开启事务
            try(PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)){
                setBookSql(book,ps);

                int affected = ps.executeUpdate();
                if(affected == 0){
                    conn.rollback();
                    throw new SQLException("创建图书失败，未插入任何行");
                }

                Long bookId;
                try(ResultSet rs = ps.getGeneratedKeys()){
                    if(rs.next()){
                        bookId = rs.getLong(1);
                        System.out.println(bookId);
                        book.setId(bookId);
                    }else{
                        conn.rollback();
                        throw new SQLException("创建图书失败，未获取到ID");
                    }
                }
            }
            conn.commit();
            return true;
        } catch (SQLException e) {
            logger.error("保存图书失败: {}", book.getTitle(), e);
            return false;
        }
    }

    /**
     * 更新库存 更新后后端返回？
     */
    // TODO
    @Override
    public boolean update(Book book) {
        String sql = "UPDATE t_book SET " +
                "title = ?, author = ?, publisher = ?, isbn = ?, stock = ?, " +
                "category = ?, available = ?, updated_at = NOW() " +
                "WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            setBookSql(book, ps);

            int affected = ps.executeUpdate();
            return affected > 0;
        } catch (SQLException e) {
            logger.error("更新图书失败: id={}", book.getId(), e);
            return false;
        }
    }

    /**
     * 根据 ISBN 查询图书
     */
    @Override
    public Optional<Book> findByIsbn(String isbn){
        String sql = "SELECT * FROM t_book WHERE isbn =? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            Book book = queryRunner.query(conn, sql, new BeanHandler<>(Book.class), isbn);
            return Optional.ofNullable(book);
        } catch (SQLException e) {
            logger.error("根据 ISBN 查询图书失败: isbn={}", isbn, e);
            return Optional.empty();
        }
    }

    /**
     * 根据分类 ID 查询图书
     */
    @Override
    public List<Book> findByCategoryId(String categoryId) {
        String sql = "SELECT * FROM t_book WHERE category_id = ? AND is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Book.class), categoryId);
        } catch (SQLException e) {
            logger.error("根据分类ID查询图书失败: categoryId={}", categoryId, e);
            return List.of();
        }
    }

    /**
     * 用于实现模糊查询的接口
     * @param keyword 搜索关键词  当不选择查询类型时  主要用于用户的查询
     * @return
     */
    @Override
    public List<Book> searchBooks(String keyword) {
        String sql = "SELECT * FROM t_book WHERE " +
                "is_deleted = 0 AND (" +
                "  title LIKE ? OR " +
                "  author LIKE ? OR " +
                "  category LIKE ? OR " +
                "  publisher LIKE ? " +
                ") ORDER BY created_at DESC";

        String queryParam = "%" + keyword + "%";

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Book.class),
                    queryParam, queryParam, queryParam, queryParam);
        } catch (SQLException e) {
            logger.error("组合模糊查询图书失败: keyword={}", keyword, e);
            return List.of();
        }
    }

    /**
     * 按照搜索类型和搜索关键字进行查询
     * @param keyword
     * @param searchType
     * @return book的列表
     */
    @Override
    public List<Book> searchBooksByField(String keyword, String searchType) {
        String sql = "SELECT * FROM t_book WHERE is_deleted = 0 AND " + searchType + " LIKE ? ORDER BY created_at DESC";
        String queryParam = "%" + keyword + "%";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Book.class), queryParam);
        } catch (SQLException e) {
            logger.error("按 {} 搜索图书失败: keyword={}", searchType, keyword, e);
            return List.of();
        }
    }
    /**
     * 更新图书库存和可用数量
     * @param isbn 图书ISBN
     * @param newStock 新库存数量
     * @param newAvailable 新可用数量
     * @return 是否更新成功
     */
    @Override
    public boolean updateStockAndAvailable(String isbn, int newStock, int newAvailable) {
        String sql = "UPDATE t_book SET stock = ?, available = ?, updated_at = NOW() WHERE isbn = ?";

        try (Connection conn = DBUtil.getConnection()) {
            int rows = queryRunner.update(conn, sql, newStock, newAvailable, isbn);
            return rows > 0;
        } catch (SQLException e) {
            logger.error("更新图书库存和可用数量失败: isbn={}, newStock={}, newAvailable={}",
                    isbn, newStock, newAvailable, e);
            return false;
        }
    }

    /**
     * 根据 ID 查询图书
     */
    @Override
    public Optional<Book> findById(Long id){
        String sql = "SELECT * FROM t_book WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()){
            Book book = queryRunner.query(conn, sql, new BeanHandler<>(Book.class), id);
            return Optional.ofNullable(book);
        }catch (SQLException e){
            logger.error("根据ID 查询图书失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 查询所有图书
     */
    @Override
    public List<Book> findAll() {
        String sql = "SELECT * FROM t_book WHERE is_deleted = 0 ORDER BY created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Book.class));
        } catch (SQLException e) {
            logger.error("查询所有图书失败", e);
            return List.of();
        }
    }

    /**
     * 根据 ID 删除图书
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_book SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, id);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除图书失败: id={}", id, e);
            return false;
        }
    }

    /**
     * 用isbn删除图书
     * @param isbn
     * @return
     */
    @Override
    public boolean deleteByIsbn(String isbn) {
        String sql = "UPDATE t_book SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE isbn = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, isbn);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除图书失败: id={}", isbn,e);
            return false;
        }
    }



    /**
     * 统计图书总数
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_book WHERE is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计图书数量失败", e);
            return 0;
        }
    }


    @Override
    public long count(String bookId) {
        String sql = "SELECT COUNT(*) FROM t_book_item WHERE book_id = ?";
        try(Connection conn = DBUtil.getConnection()){
            Long count = queryRunner.query(conn, sql, new ScalarHandler<>(), bookId);
            return count != null ? count : 0L;
        }catch (SQLException e){
            logger.error("统计图书副本数量失败: bookId={}", bookId, e);
            return 0L;
        }
    }

    /**
     * 一个返回 图书 ID 的save函数
     * @param book
     * @return
     */
//    @Override
//    public Long save_id(Book book){
//        String sql="INSERT INTO t_book (isbn,title,author,category_id,publisher,publish_date)"+"VALUES(?,?,?,?,?,?)";
//
//        try(Connection conn = DBUtil.getConnection()){
//            long id=queryRunner.insert(conn,sql,new ScalarHandler<>(),
//                    book.getIsbn(),
//                    book.getTitle(),
//                    book.getAuthor(),
//                    book.getCategoryId(),
//                    book.getPublisher());
//            return id > 0 ? id : null;
//        }catch (SQLException e){
//            logger.error("保存图书并返回ID 失败:{}",book.getTitle(),e);
//            return null;
//        }
}
